<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Warranty;
use Illuminate\Support\Facades\DB;

class DealerScoreStat extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:DealerScoreStat';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';
//sara 2025.5.14前计算分值
    // protected const TYPE_SCORE = [
    //     'MPD'   => 1.5,
    //     'LUX'   => 1,
    //     'PROTEX'=> 1,
    //     'ARES'  => 0.66,
    //     'X5'    => 5,
    //     'X2'    => 3,
    //     'X BLACK'   => 1.5,
    //     'AP'        => 1,
    //     'GL'        => 0.33,
    //     '6米'       => 0.5,
    //     '15米'      => 1.5,
    //     'ASP-M'     => 0.666,
    //     'ASP-A'     => 1,
    //     'ASP-M+'    => '',
    //     'ASP-M+ 哑光膜' => 1,
    //     'ASP-M PLUS' => 0.666,
    //     'ASP-M+ 122' => 0.53,
    // ];
    
        protected const TYPE_SCORE = [
        'MPD'   => 1.5,
        'LUX'   => 1,
        'PROTEX'=> 0.2,
        'ARES'  => 0.8,
        'EX12'  => 0.5,
        'EX15'  => 0.6,
        'X5'    => 5,
        'X2'    => 3,
        'X BLACK'   => 1.5,
         'XP20'   => 5,
         'XP40'   => 5,
         'XP55'   => 5,
         'XP70'   => 5,
         'XP70 184'   => 6,
         'XP70 90'   =>3,
         'X BLUE 80 '   => 1,
        'AP'        => 1,
        'GL'        => 0.33,
        '6米'       => 0.5,
        '15米'      => 1.5,
        'ASP-M'     => 0.666,
        'ASP-A'     => 1,
        'ASP-M+'    => '',
        'ASP-M+ 哑光膜' => 1,
        'ASP-M PLUS' => 0.666,
        'ASP-M+ 122' => 0.53,
    ];

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        /*$start_date = $this->argument('start_date');
        $end_date = $this->argument('end_date');
        $this->statOldData($start_date, $end_date);*/
        $this->statOldData(date('Y-m-d', strtotime('-1 day')), date('Y-m-d'));
        return 0;
    }

    public function statOldData($start_date, $end_date)
    {
        $start_month = strtotime($start_date);
        $end_month = strtotime($end_date);

        for ($time = $start_month; $time < $end_month; $time = strtotime(date('Y-m', $time) . ' +1 month')) {
            echo $time; echo PHP_EOL;
            $start_date = date('Y-m-d', $time);
            $end_date = date('Y-m-d', strtotime(date('Y-m', $time) . ' +1 month'));

            $this->statRolls($start_date, $end_date);
            $this->returnRolls($start_date, $end_date);
            $this->statPrice($start_date, $end_date);
            $this->statTransfer($start_date, $end_date);
        }
    }

    public function statRolls(string $beginTime, string $endTime)
    {
        $start_date = date('Y-m-01', strtotime($beginTime));
        $rollSales = DB::table('roll_sales')
            ->leftJoin('roll_master', 'roll_master.id', 'roll_sales.roll_id')
            ->leftJoin('film_type_master', 'film_type_master.id', 'roll_master.film_type_id')
            ->leftJoin('users', 'users.id', 'roll_sales.sold_to_user_id')
            ->where('roll_sales.created_at', '>=', $beginTime)
            ->where('roll_sales.created_at', '<', $endTime)
            ->where('roll_sales.type', 1)
            ->select('roll_sales.*', 'film_type_master.english_value', 'users.user_type', 'users.id as user_id', 'users.creator_id', 'film_type_master.id as film_type_id', 'users.zsdj', 'film_type_master.product_type_id', 'roll_master.length as rm_length')
            ->get();
        if (empty($rollSales)) {
            return;
        }
        foreach ($rollSales as $item) {
            if (empty($item)) {
                continue;
            }
            if ($item->rm_length <= 0) {
                continue;
            }
            if (empty($item->film_type_id)) {
                continue;
            }
            if (empty($item->sold_by_user_id) || empty($item->user_id) || empty($item->sold_to_user_id)) {
                continue;
            }

            $qm_score = 0;
            $win_score = 0;
            $max_roll = 0;
            if (empty($item->english_value)) {
                continue;
            }

            $product_type_id = in_array($item->product_type_id, [1, 3]) ? 1 : 2;
            $per_score = $this->getRollScore($item->english_value);

            if (in_array($item->film_type_id, [27, 74])) {
                $num = round($item->length / 1500, 2);
            } else {
                $num = round($item->length / $item->rm_length, 2);
            }

            if ($product_type_id == 1) {
                $qm_score += $num * $per_score;
            }
            if ($product_type_id == 2) {
                $win_score += $num * $per_score;
            }

            $price = 0;

            if ($item->sold_by_user_id == 1) {
                $distRoll = DB::table('xpel_dealer_roll_stat')
                    ->where('film_type_id', $item->film_type_id)
                    ->where('dealer_id', $item->sold_by_user_id)
                    ->where('create_month', $start_date)
                    ->first();
                if (empty($distRoll)) {
                    $insertData = [
                        'dealer_id' => $item->sold_by_user_id,
                        'dist_id' => $item->sold_by_user_id == 1 ? 0 : 1,
                        'film_type_id' => $item->film_type_id,
                        'film_name' => $item->english_value,
                        'product_type_id' => $product_type_id,
                        'num' => $num,
                        'score' => $product_type_id == 2 ? $win_score : $qm_score,
                        'create_month' => $start_date,
                    ];
                    DB::table('xpel_dealer_roll_stat')->insert($insertData);
                } else {
                    $updateData = [
                        'num' => $num + $distRoll->num,
                        'score' => $distRoll->score + ($product_type_id == 2 ? $win_score : $qm_score),
                    ];
                    DB::table('xpel_dealer_roll_stat')
                        ->where('id', $distRoll->id)
                        ->update($updateData);
                }

                $dealerStat = DB::table('xpel_dealer_stat')
                    ->where('dealer_id', $item->sold_by_user_id)
                    ->where('create_month', $start_date)
                    ->first();
                if (empty($dealerStat)) {
                    $insertData = [
                        'dealer_id' => $item->sold_by_user_id,
                        'dist_id' => $item->sold_by_user_id == 1 ? 0 : 1,
                        'qm_score' => $qm_score,
                        'win_score' => $win_score,
                        'price' => 0,
                        'create_month' => $start_date,
                    ];
                    DB::table('xpel_dealer_stat')->insert($insertData);
                } else {
                    $updateData = [
                        'qm_score' => $dealerStat->qm_score + $qm_score,
                        'win_score' => $dealerStat->win_score + $win_score,
                    ];
                    DB::table('xpel_dealer_stat')
                        ->where('id', $dealerStat->id)
                        ->update($updateData);
                }
            }

            if ($item->user_type == 3 && $item->sold_by_user_id == 1 && !empty($item->creator_id)) {
                $distRoll = DB::table('xpel_dealer_roll_stat')
                    ->where('film_type_id', $item->film_type_id)
                    ->where('dealer_id', $item->creator_id)
                    ->where('create_month', $start_date)
                    ->first();
                if (empty($distRoll)) {
                    $insertData = [
                        'dealer_id' => $item->creator_id,
                        'dist_id'       => 1,
                        'film_type_id'  => $item->film_type_id,
                        'film_name' => $item->english_value,
                        'product_type_id' => $product_type_id,
                        'num'   =>  $num,
                        'score' => $product_type_id == 2 ? $win_score : $qm_score,
                        'create_month'  => $start_date,
                    ];
                    DB::table('xpel_dealer_roll_stat')->insert($insertData);
                } else {
                    $updateData = [
                        'num'   => $num + $distRoll->num,
                        'score' => $distRoll->score + ($product_type_id == 2 ? $win_score : $qm_score),
                    ];
                    DB::table('xpel_dealer_roll_stat')
                        ->where('id', $distRoll->id)
                        ->update($updateData);
                }

                $dealerStat = DB::table('xpel_dealer_stat')
                    ->where('dealer_id', $item->creator_id)
                    ->where('create_month', $start_date)
                    ->first();
                if (empty($dealerStat)) {
                    $insertData = [
                        'dealer_id' => $item->creator_id,
                        'dist_id'       => 1,
                        'qm_score'     => $qm_score,
                        'win_score' => $win_score,
                        'price'     => 0,
                        'create_month'  => $start_date,
                    ];
                    DB::table('xpel_dealer_stat')->insert($insertData);
                } else {
                    $updateData = [
                        'qm_score'  => $dealerStat->qm_score + $qm_score,
                        'win_score' => $dealerStat->win_score + $win_score,
                    ];
                    DB::table('xpel_dealer_stat')
                        ->where('id', $dealerStat->id)
                        ->update($updateData);
                }
            }

            $distRoll = DB::table('xpel_dealer_roll_stat')
                ->where('film_type_id', $item->film_type_id)
                ->where('dealer_id', $item->user_id)
                ->where('create_month', $start_date)
                ->first();

            if (empty($distRoll)) {
                $insertData = [
                    'dealer_id' => $item->user_id,
                    'dist_id'       => $item->user_type == 2 ? $item->user_id : $item->creator_id,
                    'film_type_id'  => $item->film_type_id,
                    'film_name' => $item->english_value,
                    'product_type_id' => $product_type_id,
                    'num'   =>  $num,
                    'score' => $product_type_id == 2 ? $win_score : $qm_score,
                    'create_month'  => $start_date,
                ];
                DB::table('xpel_dealer_roll_stat')->insert($insertData);
            } else {
                $updateData = [
                    'num'   => $num + $distRoll->num,
                    'score' => $distRoll->score + ($product_type_id == 2 ? $win_score : $qm_score),
                ];
                DB::table('xpel_dealer_roll_stat')
                    ->where('id', $distRoll->id)
                    ->update($updateData);
            }

            $dealerStat = DB::table('xpel_dealer_stat')
                ->where('dealer_id', $item->user_id)
                ->where('create_month', $start_date)
                ->first();
            if (empty($dealerStat)) {
                $insertData = [
                    'dealer_id' => $item->user_id,
                    'dist_id'       => $item->user_type == 2 ? $item->user_id : $item->creator_id,
                    'qm_score'     => $qm_score,
                    'win_score' => $win_score,
                    'price'     => $price,
                    'create_month'  => $start_date,
                ];
                DB::table('xpel_dealer_stat')->insert($insertData);
            } else {
                $updateData = [
                    'qm_score'  => $dealerStat->qm_score + $qm_score,
                    'win_score' => $dealerStat->win_score + $win_score,
                    'price'     => $dealerStat->price + $price,
                ];
                DB::table('xpel_dealer_stat')
                    ->where('id', $dealerStat->id)
                    ->update($updateData);
            }
        }
    }

    public function statDealerRoll()
    {

    }

    public function returnRolls(string $start_date, string $end_time)
    {
        $start_time = date('Y-m-01', strtotime($start_date));
        $returns = DB::table('roll_return')->leftJoin('roll_master', 'roll_master.id', 'roll_return.roll_id')
            ->leftJoin('users', 'users.id', 'roll_return.user_id')
            ->leftJoin('film_type_master', 'film_type_master.id', 'roll_master.film_type_id')
            ->where('roll_return.updated_at', '>=', $start_date)
            ->where('roll_return.updated_at', '<', $end_time)
            ->select('roll_return.*', 'film_type_master.english_value', 'users.creator_id', 'users.user_type',  'film_type_master.product_type_id', 'roll_master.film_type_id', 'roll_master.length as master_length')
            ->get();

        if (empty($returns) || $returns->isEmpty()) {
            return;
        }

        foreach ($returns as $return) {
            if ($return->master_length <= 0) {
                continue;
            }
            if (in_array($return->film_type_id, [27, 74])) {
                $len = round($return->length / 1500, 2);
            } else {
                $len = round($return->length / $return->master_length, 2);
            }

            $qm_score = 0;
            $win_score = 0;

            $per_score = $this->getRollScore($return->english_value);
            $product_type_id = in_array($return->product_type_id, [1, 3]) ? 1 : 2;
            if ($product_type_id == 1) {
                $qm_score += $len * $per_score;
            }
            if ($product_type_id == 2) {
                $win_score += $len * $per_score;
            }

            $distRoll = DB::table('xpel_dealer_roll_stat')
                ->where('film_type_id', $return->film_type_id)
                ->where('dealer_id', 1)
                ->where('create_month', $start_time)
                ->first();
            if (empty($distRoll)) {
                $insertData = [
                    'dealer_id' => 1,
                    'dist_id'       => 0,
                    'film_type_id'  => $return->film_type_id,
                    'film_name' => $return->english_value,
                    'product_type_id' => $product_type_id,
                    'num'   =>  -$len,
                    'score' => $product_type_id == 2 ? -$win_score : -$qm_score,
                    'create_month'  => $start_time,
                ];
                DB::table('xpel_dealer_roll_stat')->insert($insertData);
            } else {
                $updateData = [
                    'num'   => $distRoll->num - $len,
                    'score' => $distRoll->score - ($product_type_id == 2 ? $win_score : $qm_score),
                ];
                DB::table('xpel_dealer_roll_stat')
                    ->where('id', $distRoll->id)
                    ->update($updateData);
            }

            $dealerStat = DB::table('xpel_dealer_stat')
                ->where('dealer_id', 1)
                ->where('create_month', $start_time)
                ->first();
            if (empty($dealerStat)) {
                $insertData = [
                    'dealer_id' => 1,
                    'dist_id'       => 0,
                    'qm_score'     => -$qm_score,
                    'win_score' => -$win_score,
                    'price'     => 0,
                    'create_month'  => $start_time,
                ];
                DB::table('xpel_dealer_stat')->insert($insertData);
            } else {
                $updateData = [
                    'qm_score'  => $dealerStat->qm_score - $qm_score,
                    'win_score' => $dealerStat->win_score - $win_score,
                ];
                DB::table('xpel_dealer_stat')
                    ->where('id', $dealerStat->id)
                    ->update($updateData);
            }

            $distRoll = DB::table('xpel_dealer_roll_stat')
                ->where('film_type_id', $return->film_type_id)
                ->where('dealer_id', $return->user_id)
                ->where('create_month', $start_time)
                ->first();
            if (empty($distRoll)) {
                $insertData = [
                    'dealer_id' => $return->user_id,
                    'dist_id'       => $return->user_type == 2 ? $return->user_id : ($return->creator_id ?: 0),
                    'film_type_id'  => $return->film_type_id,
                    'film_name' => $return->english_value,
                    'product_type_id' => $product_type_id,
                    'num'   =>  -$len,
                    'score' => $product_type_id == 2 ? -$win_score : -$qm_score,
                    'create_month'  => $start_time,
                ];
                DB::table('xpel_dealer_roll_stat')->insert($insertData);
            } else {
                $updateData = [
                    'num'   =>  $distRoll->num - $len,
                    'score' => $distRoll->score - ($product_type_id == 2 ? $win_score : $qm_score),
                ];
                DB::table('xpel_dealer_roll_stat')
                    ->where('id', $distRoll->id)
                    ->update($updateData);
            }

            $dealerStat = DB::table('xpel_dealer_stat')
                ->where('dealer_id', $return->user_id)
                ->where('create_month', $start_time)
                ->first();
            if (empty($dealerStat)) {
                $insertData = [
                    'dealer_id' => $return->user_id,
                    'dist_id'       => $return->user_type == 2 ? $return->user_id : ($return->creator_id ?: 0),
                    'qm_score'     => -$qm_score,
                    'win_score' => -$win_score,
                    'create_month'  => $start_time,
                ];
                DB::table('xpel_dealer_stat')->insert($insertData);
            } else {
                $updateData = [
                    'qm_score'  => $dealerStat->qm_score - $qm_score,
                    'win_score' => $dealerStat->win_score - $win_score,
                ];
                DB::table('xpel_dealer_stat')
                    ->where('id', $dealerStat->id)
                    ->update($updateData);
            }
        }
    }

    public function statTransfer(string $start_date, string $end_time)
    {
        $transfers = DB::table('roll_transfer')->leftJoin('roll_master', 'roll_master.id', 'roll_transfer.roll_id')
            ->leftJoin('users', 'users.id', 'roll_transfer.transfer_to_user_id')
            ->leftJoin('film_type_master', 'film_type_master.id', 'roll_master.film_type_id')
            ->where('roll_transfer.approved_date', '>=', $start_date)
            ->where('roll_transfer.approved_date', '<', $end_time)
            ->where('roll_transfer.approved', 1)
            ->select('roll_transfer.*', 'film_type_master.english_value', 'users.creator_id', 'users.user_type',  'film_type_master.product_type_id', 'roll_master.film_type_id', 'roll_master.length as master_length')
            ->get();
//        var_dump($transfers);
        $start_time = date('Y-m-01', strtotime($start_date));

        if (empty($transfers) || $transfers->isEmpty()) {
            return;
        }

        foreach ($transfers as $return) {
            if ($return->master_length <= 0) {
                continue;
            }
            if (in_array($return->film_type_id, [27, 74])) {
                $len = round($return->length / 1500, 2);
            } else {
                $len = round($return->length / $return->master_length, 2);
            }

            $qm_score = 0;
            $win_score = 0;

            $per_score = $this->getRollScore($return->english_value);
            $product_type_id = in_array($return->product_type_id, [1, 3]) ? 1 : 2;
            if ($product_type_id == 1) {
                $qm_score += $len * $per_score;
            }
            if ($product_type_id == 2) {
                $win_score += $len * $per_score;
            }

            $transferByUser = DB::table('users')->where('id', $return->transfer_by_user_id)->select('id', 'user_type', 'creator_id', 'user_type')->first();
            if ($return->user_type == 3 || ($transferByUser->id != $return->creator_id && $return->transfer_to_user_id != $transferByUser->creator_id)) {
                $this->operateData($return->film_type_id, $return->transfer_to_user_id, $return->user_type == 2 ? 1 : $return->creator_id, $start_time, $return->english_value, $product_type_id, $len, $win_score, $qm_score);
            }

            if ($transferByUser->user_type == 3 || ($transferByUser->id != $return->creator_id && $return->id != $transferByUser->creator_id)) {
                $this->operateData($return->film_type_id, $transferByUser->id, $transferByUser->user_type == 2 ? 1 : $transferByUser->creator_id, $start_time, $return->english_value, $product_type_id, -$len, -$win_score, -$qm_score);
            }

            if ($return->user_type == 3 && (($return->creator_id != $transferByUser->creator_id && $transferByUser->user_type == 3) || ($return->creator_id != $transferByUser->id && $transferByUser->user_type == 2))) {
                $this->operateData($return->film_type_id, $return->creator_id, 1, $start_time, $return->english_value, $product_type_id, $len, $win_score, $qm_score);
            }
            if ($transferByUser->user_type == 3 && (($return->creator_id != $transferByUser->creator_id && $return->user_type == 3) || ($return->transfer_to_user_id != $transferByUser->creator_id && $return->user_type == 2))) {
                $this->operateData($return->film_type_id, $transferByUser->creator_id, 1, $start_time, $return->english_value, $product_type_id, -$len, -$win_score, -$qm_score);
            }
        }
    }

    public function operateData($film_type_id, $user_id, $creator_id, $start_date, $english_value, $product_type_id, $len, $win_score, $qm_score)
    {
        $distRoll = DB::table('xpel_dealer_roll_stat')
            ->where('film_type_id', $film_type_id)
            ->where('dealer_id', $user_id)
            ->where('create_month', $start_date)
            ->first();
        $start_time = date('Y-m-01', strtotime($start_date));
        if (empty($distRoll)) {
            $insertData = [
                'dealer_id' => $user_id,
                'dist_id'       => $creator_id,
                'film_type_id'  => $film_type_id,
                'film_name' => $english_value,
                'product_type_id' => $product_type_id,
                'num'   =>  $len,
                'score' => $product_type_id == 2 ? $win_score : $qm_score,
                'create_month'  => $start_time,
            ];
            DB::table('xpel_dealer_roll_stat')->insert($insertData);
        } else {
            $updateData = [
                'num'   =>  $distRoll->num + $len,
                'score' => $distRoll->score + ($product_type_id == 2 ? $win_score : $qm_score),
            ];
            DB::table('xpel_dealer_roll_stat')
                ->where('id', $distRoll->id)
                ->update($updateData);
        }

        $dealerStat = DB::table('xpel_dealer_stat')
            ->where('dealer_id', $user_id)
            ->where('create_month', $start_time)
            ->first();
        if (empty($dealerStat)) {
            $insertData = [
                'dealer_id' => $user_id,
                'dist_id'       => $creator_id,
                'qm_score'     => $qm_score,
                'win_score' => $win_score,
                'create_month'  => $start_time
            ];
            DB::table('xpel_dealer_stat')->insert($insertData);
        } else {
            $updateData = [
                'qm_score'  => $dealerStat->qm_score + $qm_score,
                'win_score' => $dealerStat->win_score + $win_score,
            ];
            DB::table('xpel_dealer_stat')
                ->where('id', $dealerStat->id)
                ->update($updateData);
        }
    }

    public function statPrice(string $start_date, string $end_time)
    {
        $data = DB::table('xpel_dealer_roll_stat')
            ->leftJoin('users', 'users.id', 'xpel_dealer_roll_stat.dealer_id')
            ->where('users.user_type', 3)
            ->where('create_month', '>=', $start_date)
            ->where('create_month', '<', $end_time)
            ->where('film_type_id', 65)
            ->groupBy('dealer_id')
            ->select(DB::Raw('SUM(num) AS total'), 'dealer_id as user_id', 'users.zsdj', 'users.creator_id')
            ->get();

        $start_time = date('Y-m-01', strtotime($start_date));
        if (empty($data)) {
            return;
        }

        foreach ($data as $item) {
            $price = 0;
            if (strcmp($item->zsdj, 3) === 0) {
                $price = $item->total * 700;
            }
            if (strcmp($item->zsdj, 5) === 0) {
                $price = $item->total * 1500;
            }
            if (empty($price) || $price <= 0) {
                continue;
            }

            $dealerStat = DB::table('xpel_dealer_stat')
                ->where('dealer_id', $item->user_id)
                ->where('create_month', $start_time)
                ->first();

            if (empty($dealerStat)) {
                $insertData = [
                    'dealer_id' => $item->user_id,
                    'dist_id'       => $item->creator_id,
                    'price'     => $price,
                    'create_month'  =>  $start_time,
                ];
                DB::table('xpel_dealer_stat')->insert($insertData);
            } else {
                $updateData = [
                    'price' => $price
                ];
                DB::table('xpel_dealer_stat')
                    ->where('id', $dealerStat->id)
                    ->update($updateData);
            }
        }
    }

    /**
     * 获取卷号的分数
     *
     * @param string $name
     * @return int|mixed
     */
    public function getRollScore(string $name)
    {
        $score = 0;
        foreach (self::TYPE_SCORE as $key => $item) {
            if (mb_strpos($name, $key) === 0) {
                $score = $item;
                break;
            }
        }

        return $score;
    }
}
